package com.hr.salary.dao.impl;

import java.sql.SQLException;
import java.util.Calendar;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.Test;

import com.hr.salary.dao.SalaryDao;
import com.hr.salary.domain.Salary;
import com.hr.salary.domain.SalaryRS;
import com.hr.user.dao.Impl.UserDaoImpl;
import com.hr.user.domain.User;
import com.hr.utils.PageBean;
import com.hr.utils.TxQueryRunner;

public class SalaryDaoImpl implements SalaryDao {
	private QueryRunner qr = new TxQueryRunner();
	private UserDaoImpl userDaoImpl = new UserDaoImpl();
	
	@Override
	public PageBean<Salary> queryAll_Page(int PageCurrent, int PageSize) {
		// TODO Auto-generated method stub
		try {
			PageBean<Salary> pb = new PageBean<Salary>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from SALARY_TABLE";
			Number num = (Number)qr.query(sql, new ScalarHandler());
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			sql = "select * from (select a1.*,rownum rn from (select * from SALARY_TABLE)a1 where rownum<="+PageCurrent*PageSize+")where rn >="+((PageCurrent-1)*PageSize+1);
			List<Salary> list_salary = qr.query(sql, new BeanListHandler<Salary>(Salary.class));
			for(int i=0;i<list_salary.size();i++) {
				list_salary.get(i).setUsername(userDaoImpl.findByUUID(list_salary.get(i).getUser_id()).getUsername());
			}
			pb.setBeanList(list_salary);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public String findByUserID(String user_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "select salary_id from SALARY_TABLE where user_id = ?";
			String salary_id = (String) qr.query(sql, new ScalarHandler(), user_id);
			return salary_id;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public void addSalaryRP(Object[] params) {
		// TODO Auto-generated method stub
		try {
			String sql = "insert into REWARD_PUNISH values(?,?,?,?,?,?,?,?,?)";
			qr.update(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public PageBean<SalaryRS> queryAll_Page_SalaryRS(int PageCurrent, int PageSize,String salary_id) {
		// TODO Auto-generated method stub
		try {
			PageBean<SalaryRS> pb = new PageBean<SalaryRS>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from REWARD_PUNISH where salary_id = ?";
			Number num = (Number)qr.query(sql, new ScalarHandler(),salary_id);
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			String sql_template = "select hu.user_id,hu.username, st.salary_id,st.salary_basic,st.salary_real, rp.pro_name,rp.pro_description,rp.pro_money,rp.pro_id,rp.month,rp.year,rp.state "
								  +	" from HR_USER hu,SALARY_TABLE st,REWARD_PUNISH rp " 
								  + " where hu.user_id IN ( "
								  + " select user_id from REWARD_PUNISH where rp.user_id=hu.user_id "
								  + " ) and st.salary_id IN ( "
								  + " select salary_id from REWARD_PUNISH where rp.salary_id=st.salary_id "
								  + " ) and hu.user_id = st.user_id and rp.salary_id = ?";
			sql = "select * from (select a1.*,rownum rn from ("+sql_template+")a1 where rownum<="+PageCurrent*PageSize+")where rn >="+((PageCurrent-1)*PageSize+1);
			List<SalaryRS> list_salary = qr.query(sql, new BeanListHandler<SalaryRS>(SalaryRS.class),salary_id);
			pb.setBeanList(list_salary);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Test
	public void test() {
		PageBean<SalaryRS> pageBean = queryAll_Page_SalaryRS(1,4,"0272DB2793384059ADFAC6D4FB6964BD");
		System.out.println(pageBean.getBeanList());
	}
	
	@Override
	public void checkPass(String pro_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "update REWARD_PUNISH set state = '1' where pro_id=?";
			qr.update(sql,pro_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public void checkPassUpdateSalaryReal(String salary_id, String user_id,String pro_money,String month,String year) {
		// TODO Auto-generated method stub
		try {
			String sql = "update SALARY_TABLE set salary_real = salary_real + ? where salary_id = ? and user_id = ? and month = ? and year = ?";
			double money = Double.parseDouble(pro_money);
			Object[] params = {money,salary_id,user_id,month,year};
			qr.update(sql, params);
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public void checkRefuse(String pro_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "update REWARD_PUNISH set state = '-1' where pro_id=?";
			qr.update(sql,pro_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public void checkRefuseUpdateSalaryReal(String salary_id, String user_id,
			String pro_money, String month, String year) {
		// TODO Auto-generated method stub
		try {
			String sql = "update SALARY_TABLE set salary_real = salary_real - ? where salary_id = ? and user_id = ? and month = ? and year = ?";
			double money = Double.parseDouble(pro_money);
			Object[] params = {money,salary_id,user_id,month,year};
			qr.update(sql, params);
		} catch (NumberFormatException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public Salary findBySalaryId(String salary_id) {
		// TODO Auto-generated method stub
		try {
			String sql = "select * from SALARY_TABLE where salary_id=?";
			return qr.query(sql, new BeanHandler<Salary>(Salary.class),salary_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public void updateSalary(Object[] params) {
		// TODO Auto-generated method stub
		try {
			String sql = "update SALARY_TABLE set salary_real = salary_real - salary_basic + ? , salary_basic = ? where salary_id = ? and user_id = ? and month = ? and year = ?";
			qr.update(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public PageBean<SalaryRS> queryAll_Page_SalaryRS(int PageCurrent,
			int PageSize, User user) {
		// TODO Auto-generated method stub
		try {
			PageBean<SalaryRS> pb = new PageBean<SalaryRS>();
			//设置当前页面页码
			pb.setPageCurrent(PageCurrent);
			//设置当前页面的页面容量
			pb.setPageSize(PageSize);
			String sql = "select Count(*) from REWARD_PUNISH where user_id = ?";
			Number num = (Number)qr.query(sql, new ScalarHandler(),user.getUser_id());
			int rowCount = num.intValue();//获取数据库中的所有记录数
			pb.setRowCount(rowCount);
			String sql_template = "select hu.user_id,hu.username, st.salary_id,st.salary_basic,st.salary_real, rp.pro_name,rp.pro_description,rp.pro_money,rp.pro_id,rp.month,rp.year,rp.state "
								  +	" from HR_USER hu,SALARY_TABLE st,REWARD_PUNISH rp " 
								  + " where hu.user_id IN ( "
								  + " select user_id from REWARD_PUNISH where rp.user_id=hu.user_id "
								  + " ) and st.salary_id IN ( "
								  + " select salary_id from REWARD_PUNISH where rp.salary_id=st.salary_id "
								  + " ) and hu.user_id = st.user_id and rp.user_id = ?";
			sql = "select * from (select a1.*,rownum rn from ("+sql_template+")a1 where rownum<="+PageCurrent*PageSize+")where rn >="+((PageCurrent-1)*PageSize+1);
			List<SalaryRS> list_salary = qr.query(sql, new BeanListHandler<SalaryRS>(SalaryRS.class),user.getUser_id());
			pb.setBeanList(list_salary);
			return pb;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public List<Salary> findSalaryByUserID(String user_id) {
		// TODO Auto-generated method stub
		try {
			Calendar c = Calendar.getInstance();
			int year = c.get(Calendar.YEAR);
			String sql = "select * from SALARY_TABLE where user_id=? and year='"+year+"' order by month asc";
			return qr.query(sql, new BeanListHandler<Salary>(Salary.class),user_id);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(e);
		}
	}
	
	@Override
	public void addSalary(Salary salary) {
		// TODO Auto-generated method stub
		try {
			String sql = "insert into SALARY_TABLE values(?,?,?,?,?,?)";
			Object[] params = {salary.getSalary_id(),salary.getUser_id(),salary.getSalary_basic(),
					salary.getSalary_basic(),salary.getMonth(),salary.getYear()};
			qr.update(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	@Override
	public void deleteApplyByPerson(String pro_id, String salary_id,
			String user_id, String month, String year) {
		// TODO Auto-generated method stub
		try {
			String sql = "delete REWARD_PUNISH where pro_id=? and salary_id=? and user_id=? and month=? and year=?";
			Object[] params = {pro_id,salary_id,user_id,month,year};
			qr.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
}
